﻿using DBUtil.Attributes;
using DotNetCommon.Extensions;
using NUnit.Framework;
using Shouldly;
using System.ComponentModel.DataAnnotations.Schema;

namespace Test.MySql.JsonTests.Primitives
{
    [TestFixture]
    internal class SimpleTypeTests_number : TestBase
    {
        #region model
        [Table("test")]
        public class Person
        {
            [PrimaryKey(KeyStrategy = KeyStrategy.Identity)]
            [Column("id")]
            public int Id { get; set; }

            //int
            [JsonStore(Bucket = "p_int")]
            public int p_int { get; set; }
            [JsonStore(Bucket = "p_uint")]
            public uint p_uint { get; set; }
            [JsonStore(Bucket = "p_int_null")]
            public int? p_int_null { get; set; }
            [JsonStore(Bucket = "p_uint_null")]
            public uint? p_uint_null { get; set; }

            //long
            [JsonStore(Bucket = "p_long")]
            public long p_long { get; set; }
            [JsonStore(Bucket = "p_ulong")]
            public ulong p_ulong { get; set; }
            [JsonStore(Bucket = "p_long_null")]
            public long? p_long_null { get; set; }
            [JsonStore(Bucket = "p_ulong_null")]
            public ulong? p_ulong_null { get; set; }

            //float/double/decimal
            [JsonStore(Bucket = "p_double")]
            public double p_double { get; set; }
            [JsonStore(Bucket = "p_float")]
            public float p_float { get; set; }
            [JsonStore(Bucket = "p_decimal")]
            public decimal p_decimal { get; set; }
            [JsonStore(Bucket = "p_double_null")]
            public double? p_double_null { get; set; }
            [JsonStore(Bucket = "p_float_null")]
            public float? p_float_null { get; set; }
            [JsonStore(Bucket = "p_decimal_null")]
            public decimal? p_decimal_null { get; set; }
        }

        [Table("test2")]
        public class PersonKey
        {
            [PrimaryKey(KeyStrategy = KeyStrategy.Identity)]
            [Column("id")]
            public int Id { get; set; }

            //int
            [JsonStore(Bucket = "ext", Key = "p_int")]
            public int p_int { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_uint")]
            public uint p_uint { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_int_null")]
            public int? p_int_null { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_uint_null")]
            public uint? p_uint_null { get; set; }

            //long
            [JsonStore(Bucket = "ext", Key = "p_long")]
            public long p_long { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_ulong")]
            public ulong p_ulong { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_long_null")]
            public long? p_long_null { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_ulong_null")]
            public ulong? p_ulong_null { get; set; }

            //float/double/decimal
            [JsonStore(Bucket = "ext", Key = "p_double")]
            public double p_double { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_float")]
            public float p_float { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_decimal")]
            public decimal p_decimal { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_double_null")]
            public double? p_double_null { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_float_null")]
            public float? p_float_null { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_decimal_null")]
            public decimal? p_decimal_null { get; set; }
        }
        #endregion

        [SetUp]
        public void SetUp()
        {
            DropTableIfExist("test");
            db.ExecuteSql("""
                create table test(
                    id int auto_increment primary key,
                    -- int
                    p_int json,
                    p_uint json,
                    p_int_null json,
                    p_uint_null json,
                    -- long
                    p_long json,
                    p_ulong json,
                    p_long_null json,
                    p_ulong_null json,
                    -- float/double/decimal
                    p_double json,
                    p_float json,
                    p_decimal json,
                    p_double_null json,
                    p_float_null json,
                    p_decimal_null json
                )
                """);
            db.ExecuteSql("""
                insert into test(p_int,p_uint,p_int_null,p_uint_null,p_long,p_ulong,p_long_null,p_ulong_null,p_double,p_float,p_decimal,p_double_null,p_float_null,p_decimal_null) values
                ('1','2','3','4',         '5','6','7','8',         '1.1','1.2','1.3','1.4','1.5','1.6'),
                ('1','2',null,null,   '5','6',null,null,   '1.1','1.2','1.3',null,null,null),
                ('1','1','1','1',         '1','1','1','1',         '1','1','1','1','1','1');
                """);

            DropTableIfExist("test2");
            db.ExecuteSql("""
                create table test2(
                    id int auto_increment primary key,
                    ext json
                )
                """);
            db.ExecuteSql("""
                insert into test2(ext) values(json_object("p_int",10,"p_uint",20,"p_int_null",null,"p_uint_null",null,"p_long",30,"p_ulong",40,"p_long_null",null,"p_ulong_null",null,"p_double",60.1,"p_float",50.1,"p_decimal",70.1,"p_double_null",null,"p_float_null",null,"p_decimal_null",null));
                """);
        }

        #region NoKey
        [Test]
        public void TestSelect()
        {
            var select = db.Select<Person>().Where(i => i.p_int_null == null);
            var list = select.ToList();
            var json = list.ToJson();
            json.ShouldBe("""[{"Id":2,"p_int":1,"p_uint":2,"p_int_null":null,"p_uint_null":null,"p_long":5,"p_ulong":6,"p_long_null":null,"p_ulong_null":null,"p_double":1.1,"p_float":1.2,"p_decimal":1.300000000000000,"p_double_null":null,"p_float_null":null,"p_decimal_null":null}]""");
        }

        [Test]
        public void TestSelect2()
        {
            var list = db.Select<Person>().Where(i => i.p_int_null == null).ToList(i => new
            {
                i.Id,
                p_int = i.p_int + 5,
                p_int_null = i.p_int_null == null ? 0 : i.p_int_null,
            });
            var json = list.ToJson();
            json.ShouldBe("""[{"Id":2,"p_int":6,"p_int_null":0}]""");
        }

        [Test]
        public void TestInsert()
        {
            var insert = db.Insert<Person>().SetEntity(new Person()
            {
                //int
                p_int = 10,
                p_uint = 20,
                p_int_null = null,
                p_uint_null = null,
                //long
                p_long = 30,
                p_ulong = 40,
                p_long_null = null,
                p_ulong_null = null,
                //
                p_float = 50.1f,
                p_double = 60.1d,
                p_decimal = 70.1m,
                p_float_null = null,
                p_double_null = null,
                p_decimal_null = null,
            });
            var r = insert.ExecuteAffrows();
            r.ShouldBe(1);
            var dt = insert.ToDataTable();
            var json = PrintTable(dt);
            json.ShouldBe("""[{"p_int":"10","p_uint":"20","p_int_null":null,"p_uint_null":null,"p_long":"30","p_ulong":"40","p_long_null":null,"p_ulong_null":null,"p_double":"60.1","p_float":"50.1","p_decimal":"70.1","p_double_null":null,"p_float_null":null,"p_decimal_null":null}]""");
            db.BulkCopy(dt);
            var p = db.SelectScalar<int>("select count(1) from test");
            //此时应该有5行了
            p.ShouldBe(5);
        }

        [Test]
        public void TestDelete()
        {
            var delete = db.Delete<Person>().Where(i => i.p_int_null == null);
            var sql = delete.ToSql();
            sql.ShouldBe("""delete from test where (json_value(p_int_null,'$' returning signed)) is null;""");
            var r = delete.ExecuteAffrows();
            r.ShouldBe(1);
        }

        [Test]
        public void TestUpdate()
        {
            var update = db.Update<Person>().SetEntity(new Person
            {
                Id = 1,
                p_int = 2,
                p_int_null = 3,
                p_double = 4
            });
            var r = update.ExecuteAffrows();
            r.ShouldBe(1);
            var json = db.Select<Person>().ToList().ToJson();
            json.ShouldBe("""[{"Id":1,"p_int":2,"p_uint":0,"p_int_null":3,"p_uint_null":null,"p_long":0,"p_ulong":0,"p_long_null":null,"p_ulong_null":null,"p_double":4,"p_float":0,"p_decimal":0.000000000000000,"p_double_null":null,"p_float_null":null,"p_decimal_null":null},{"Id":2,"p_int":1,"p_uint":2,"p_int_null":null,"p_uint_null":null,"p_long":5,"p_ulong":6,"p_long_null":null,"p_ulong_null":null,"p_double":1.1,"p_float":1.2,"p_decimal":1.300000000000000,"p_double_null":null,"p_float_null":null,"p_decimal_null":null},{"Id":3,"p_int":1,"p_uint":1,"p_int_null":1,"p_uint_null":1,"p_long":1,"p_ulong":1,"p_long_null":1,"p_ulong_null":1,"p_double":1,"p_float":1,"p_decimal":1.000000000000000,"p_double_null":1,"p_float_null":1,"p_decimal_null":1.000000000000000}]""");
        }
        #endregion

        #region Key
        [Test]
        public void TestSelectKey()
        {
            var select = db.Select<PersonKey>().Where(i => i.p_int_null == null);
            var list = select.ToList();
            var json = list.ToJson();
            json.ShouldBe("""[{"Id":1,"p_int":10,"p_uint":20,"p_int_null":null,"p_uint_null":null,"p_long":30,"p_ulong":40,"p_long_null":null,"p_ulong_null":null,"p_double":60.1,"p_float":50.1,"p_decimal":70.100000000000000,"p_double_null":null,"p_float_null":null,"p_decimal_null":null}]""");
        }

        [Test]
        public void TestSelect2Key()
        {
            var list = db.Select<PersonKey>().Where(i => i.p_int_null == null).ToList(i => new
            {
                i.Id,
                p_int = i.p_int + 5,
                p_int_null = i.p_int_null == null ? 0 : i.p_int_null,
            });
            var json = list.ToJson();
            json.ShouldBe("""[{"Id":1,"p_int":15,"p_int_null":0}]""");
        }

        [Test]
        public void TestInsertKey()
        {
            var insert = db.Insert<PersonKey>().SetEntity(new PersonKey()
            {
                //int
                p_int = 10,
                p_uint = 20,
                p_int_null = null,
                p_uint_null = null,
                //long
                p_long = 30,
                p_ulong = 40,
                p_long_null = null,
                p_ulong_null = null,
                //
                p_float = 50.1f,
                p_double = 60.1d,
                p_decimal = 70.1m,
                p_float_null = null,
                p_double_null = null,
                p_decimal_null = null,
            });
            var r = insert.ExecuteAffrows();
            r.ShouldBe(1);
            var dt = insert.ToDataTable();
            var json = PrintTable(dt);
            json.ShouldBe("""[{"ext":"{\"p_int\":10,\"p_uint\":20,\"p_int_null\":null,\"p_uint_null\":null,\"p_long\":30,\"p_ulong\":40,\"p_long_null\":null,\"p_ulong_null\":null,\"p_double\":60.1,\"p_float\":50.1,\"p_decimal\":70.1,\"p_double_null\":null,\"p_float_null\":null,\"p_decimal_null\":null}"}]""");
            db.BulkCopy(dt);
            var p = db.SelectScalar<int>("select count(1) from test2");
            //此时应该有3行了
            p.ShouldBe(3);

            var list = db.Select<PersonKey>().ToList();
            var json2 = list.ToJson();
            json2.ShouldBe("""[{"Id":1,"p_int":10,"p_uint":20,"p_int_null":null,"p_uint_null":null,"p_long":30,"p_ulong":40,"p_long_null":null,"p_ulong_null":null,"p_double":60.1,"p_float":50.1,"p_decimal":70.100000000000000,"p_double_null":null,"p_float_null":null,"p_decimal_null":null},{"Id":2,"p_int":10,"p_uint":20,"p_int_null":null,"p_uint_null":null,"p_long":30,"p_ulong":40,"p_long_null":null,"p_ulong_null":null,"p_double":60.1,"p_float":50.1,"p_decimal":70.100000000000000,"p_double_null":null,"p_float_null":null,"p_decimal_null":null},{"Id":3,"p_int":10,"p_uint":20,"p_int_null":null,"p_uint_null":null,"p_long":30,"p_ulong":40,"p_long_null":null,"p_ulong_null":null,"p_double":60.1,"p_float":50.1,"p_decimal":70.100000000000000,"p_double_null":null,"p_float_null":null,"p_decimal_null":null}]""");
        }

        [Test]
        public void TestDeleteKey()
        {
            var delete = db.Delete<PersonKey>().Where(i => i.p_int_null == null);
            var r = delete.ExecuteAffrows();
            r.ShouldBe(1);
        }

        [Test]
        public void TestUpdateKey()
        {
            var update = db.Update<PersonKey>().SetEntity(new PersonKey
            {
                Id = 1,
                p_int = 2,
                p_int_null = 3,
                p_double = 4
            });
            var r = update.ExecuteAffrows();
            r.ShouldBe(1);
            var json = db.Select<PersonKey>().ToList().ToJson();
            json.ShouldBe("""[{"Id":1,"p_int":2,"p_uint":0,"p_int_null":3,"p_uint_null":null,"p_long":0,"p_ulong":0,"p_long_null":null,"p_ulong_null":null,"p_double":4,"p_float":0,"p_decimal":0.000000000000000,"p_double_null":null,"p_float_null":null,"p_decimal_null":null}]""");
        }
        #endregion
    }
}
